<?php
class Mod_sys extends CI_Model
{
    function __construct()
    {
        parent::__construct();
    }
	
	function get_tahun_ajaran()
	{
		$sql 	= "SELECT
				id_tahun_ajaran, tahun_ajaran
				FROM tbl_tahun_ajaran
				ORDER BY id_tahun_ajaran";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		return $res;
	}
	
	function insertLogLogin($kategori
						, $kodeuser)
	{
		$sql	= "INSERT INTO tbl_log
				(kategori, kodeuser, keterangan, `ip`, tanggal, jam)
				VALUES
				('".$kategori."', '".$kodeuser."', 'Melakukan Login', '"."IP BLOM"."', '".date('Ymd')."', '".date('his')."')";
		$rec	= $this->db->query($sql);
		
	}
	
	function insertLog($kategori
						, $kodeuser			
						, $typetraksanksi
						, $id
						, $tabel)
	{
		switch ($typetraksanksi)
		{
			case "insert":
				$keterangan = $this->getInfoUser($kategori, $kodeuser)
								." Melakukan penambahan data pada "
								//.$tabel
								.$this->translateTable($tabel)
								." dengan kode [".$id."]";
			break;	
			case "update":
				$keterangan = $this->getInfoUser($kategori, $kodeuser)
								." Melakukan perubahan data pada "
								//.$tabel
								.$this->translateTable($tabel)
								." dengan kode [".$id."]";
			break;	
			case "delete":
				$keterangan = $this->getInfoUser($kategori, $kodeuser)
								." Melakukan hapus data pada "
								//.$tabel
								.$this->translateTable($tabel)
								." dengan kode [".$id."]";
			break;	
		}
		$sql	= "INSERT INTO tbl_log 
				(kategori, kodeuser, keterangan, `ip`, tanggal, jam)
				VALUES
				('".$kategori."', '".$kodeuser."', '".$keterangan."', '"."IP BLOM"."', '".date('Ymd')."', '".date('his')."')";
		$rec	= $this->db->query($sql);		
	}
	
	function getInfoUser($kategori, $kodeuser)
	{

		switch($kategori)
		{
			case "admin":
				$sql 	= "SELECT user_name
						FROM tbl_admin_user
						WHERE id_admin_user = '".$kodeuser."'";
				$rec	= $this->db->query($sql);
				$res	= $rec->result_array();
				if($rec->num_rows()>0)
					return "Admin [".$res[0]['user_name']."]";
			break;
			case "dosen":
					return "Dosen ".$kodeuser;
			break;
			case "siswa":
					return "Mahasiswa ".$kodeuser;
			break;
		}
		
	}
	
	function translateTable($table)
	{
		switch($table)
		{
			case "tbl_kelas":
				return "Kelas";
			break;					
			case "tbl_support_online":
				return "Support Online";
			break;					
			case "tbl_academic_calender":
				return "Kalender Akademik";
			break;					
			case "tbl_page":
				return "Halaman Website";
			break;					
			case "tbl_banner":
				return "Banner Sidebar";
			break;					
			case "tbl_tahun_ajaran":
				return "Tahun Ajaran";
			break;					
			case "tbl_mata_kuliah":
				return "Mata Kuliah";
			break;					
			case "tbl_dosen":
				return "Dosen";
			break;					
			case "tbl_jurusan":
				return "Jurusan";
			break;					
			case "tbl_siswa":
				return "Mahasiswa";
			break;					
			case "tbl_kelas_detail":
				return "Kelas Detail Pertahun";
			break;					
			case "tbl_jadwal":
				return "Jadwal Matakuliah";
			break;					
			case "tbl_tugas_mengajar":
				return "Tugas Mengajar";
			break;					
			case "tbl_admin_user":
				return "Admin User";
			break;					
			case "tbl_admin_category":
				return "Admin Kategori";
			break;					
			case "tbl_content":
				return "Konten Website";
			break;					
			case "tbl_galeri":
				return "Galeri";
			break;					
			case "tbl_kategori_galeri":
				return "Kategori Galeri";
			break;					
			case "tblkontak":
				return "Kontak";
			break;					
			case "tbl_data_pmb":
				return "Data PMB";
			break;					
			case "tbl_materi":
				return "Artikel Materi Kuliah";
			break;					
			case "tbl_elearning":
				return "Elearning";
			break;					
			default:
				return $table;
			break;
		}
	}

	function get_kelas()
	{
		$sql 	= "SELECT
				kode_kelas, nama_kelas
				FROM tbl_kelas";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		return $res;
	}

	function get_kelas_nilai_siswa($nim)
	{
		$sql	= "SELECT
				k.kode_kelas, k.nama_kelas
				FROM tbl_kelas k
				INNER JOIN tbl_kelas_detail kd
					ON kd.kode_kelas = k.kode_kelas
				INNER JOIN tbl_kelas_mahasiswa ks
					ON ks.id_kelas_detail = kd.id_kelas_detail
				where ks.nim='$nim'
				GROUP BY k.kode_kelas";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		return $res;
	}

	function get_tahun_ajaran_nilai($id_guru)
	{
		$sql 	= "SELECT
				ta.id_tahun_ajaran
				, ta.tahun_ajaran
				FROM tbl_tahun_ajaran ta
				INNER JOIN tbl_kelas_detail kd
					ON kd.tahun_ajaran = ta.id_tahun_ajaran
				INNER JOIN tbl_tugas_mengajar tm
					ON tm.id_kelas_detail = kd.id_kelas_detail
				WHERE tm.kode_dosen='$id_guru'
				GROUP BY ta.tahun_ajaran
				ORDER BY ta.tahun_ajaran";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		return $res;
	}

	function get_tahun_ajaran_nilai_siswa($nim)
	{
		$sql 	= "SELECT
				ta.id_tahun_ajaran
				, ta.tahun_ajaran
				FROM tbl_tahun_ajaran ta
				INNER JOIN tbl_kelas_detail kd
					ON kd.tahun_ajaran = ta.id_tahun_ajaran
				INNER JOIN tbl_kelas_mahasiswa ks
					ON ks.id_kelas_detail = kd.id_kelas_detail
				WHERE ks.nim='$nim'
				GROUP BY ta.tahun_ajaran
				ORDER BY ta.tahun_ajaran";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		return $res;
	}
	
	function get_tahun_ajaran_nilai_siswa_transfer($nim)
	{
		$sql 	= "SELECT
				ta.id_tahun_ajaran
				, ta.tahun_ajaran
				FROM tbl_tahun_ajaran ta
				INNER JOIN tbl_nilai_mahasiswa_transfer tr
					ON tr.tahun_ajaran = ta.id_tahun_ajaran
				WHERE tr.nim='$nim'
				GROUP BY ta.tahun_ajaran
				ORDER BY ta.tahun_ajaran";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		return $res;
	}
	
	function get_semester_nilai_siswa($nim)
	{
		$sql	= "SELECT
				s.id_semester, s.semester
				FROM tbl_semester s
				INNER JOIN tbl_kelas_detail kd
					ON kd.semester = s.id_semester
				INNER JOIN tbl_kelas_mahasiswa ks
					ON ks.id_kelas_detail = kd.id_kelas_detail
				where ks.nim='$nim'
				GROUP BY s.semester";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		return $res;
	}
	
	function get_semester_nilai_siswa_transfer($nim)
	{
		$sql	= "SELECT
				s.id_semester, s.semester
				FROM tbl_semester s
				INNER JOIN tbl_nilai_mahasiswa_transfer tr
					ON tr.semester = s.id_semester
				WHERE tr.nim='$nim'
				GROUP BY s.semester";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		return $res;
	}
	
	function cek_siswa_transfer($nim)
	{
		$sql	= "SELECT
				nim
				FROM tbl_nilai_mahasiswa_transfer
				WHERE nim='$nim'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		if($rec->num_rows()>0)
			return $res;
	}

	function get_kelas_nilai($id_guru)
	{
		$sql 	= "SELECT
				k.kode_kelas, k.nama_kelas
				FROM tbl_kelas k
				INNER JOIN tbl_kelas_detail kd
					ON kd.kode_kelas = k.kode_kelas
				INNER JOIN tbl_tugas_mengajar tm
					ON kd.id_kelas_detail = tm.id_kelas_detail
				WHERE tm.kode_dosen = '$id_guru'
				GROUP BY k.kode_kelas
				ORDER BY k.kode_kelas";
				$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		return $res;
	}

	function get_kelas_from_id($id)
	{
		$sql	= "SELECT
				kode_kelas, nama_kelas
				FROM tbl_kelas
				WHERE kode_kelas='$id'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		if($rec->num_rows()>0)
			return $res[0];
		return array("kode_kelas"=>"", "nama_kelas"=>"");
	}

	function get_tahun_ajaran_from_id($id)
	{
		$sql 	= "SELECT
				id_tahun_ajaran, tahun_ajaran
				FROM tbl_tahun_ajaran
				WHERE id_tahun_ajaran='$id'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		if($rec->num_rows()>0)
			return $res[0];
		return array("id_tahun_ajaran"=>"", "tahun_ajaran"=>"");
	}
	
	
	function get_dosen_from_id($id)
	{
		$sql 	= "SELECT
				kode_dosen, nama
				FROM tbl_dosen
				WHERE kode_dosen='$id'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		if($rec->num_rows()>0)
			return $res[0];
		return array("kode_dosen"=>"", "nama"=>"");
	}
	
	
	function get_semester_from_id($id)
	{
		$sql	= "SELECT
				id_semester, semester
				FROM tbl_semester
				WHERE id_semester='$id'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		if($rec->num_rows()>0)
			return $res[0];
		return array("id_semester"=>"", "semester"=>"");
	}

	function get_mapel_from_id($id)
	{
		$sql 	= "SELECT
				kode_mata_kuliah, mata_kuliah, pertemuan
				FROM tbl_mata_kuliah
				WHERE kode_mata_kuliah='$id'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		if($rec->num_rows()>0)
			return $res[0];
		return array("kode_mata_kuliah"=>"", "mata_kuliah"=>"", "pertemuan"=>"");
	}
	
	
	function get_kat_nilai_from_id($id)
	{
		$sql 	= "SELECT
				id_kategori_nilai, kategori
				FROM tbl_kategori_nilai
				WHERE id_kategori_nilai='$id'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		if($rec->num_rows()>0)
			return $res[0];
		return array("id_kategori_nilai"=>"", "kategori"=>"");
	}
	
	function get_kelas_detail_nilai($tahun, $kelas)
	{
		$sql 	= "SELECT
				id_kelas_detail
				FROM tbl_kelas_detail
				WHERE kode_kelas='".$kelas."'
				AND tahun_ajaran='".$tahun."'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		if($rec->num_rows()>0)
		{
			return $res[0]['id_kelas_detail'];
		}
		return "";
	}

	function get_mapel($id_guru, $tahun, $kelas)
	{
		$sql 	= "SELECT
				id_kelas_detail
				FROM tbl_kelas_detail
				WHERE kode_kelas='".$kelas."'
				AND tahun_ajaran='".$tahun."'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		if($rec->num_rows()>0)
		{
			$sql2 = "SELECT
					id_tugas_mengajar,  p.kode_mata_kuliah, p.mata_kuliah
					FROM tbl_tugas_mengajar m
					INNER JOIN tbl_mata_kuliah p on m.kode_mata_kuliah = p.kode_mata_kuliah
					WHERE id_kelas_detail='".$res[0]['id_kelas_detail']."'
					AND kode_dosen='$id_guru'";
			$rec2 = $this->db->query($sql2);
			$res2 = $rec2->result_array();
			if($rec2->num_rows()>0)
				return $res2;
		}
		return null;
	}
	
	function get_mapel_siswa($nim, $tahun)
	{
		$sql = "SELECT 
				mp.kode_mata_kuliah, mp.mata_kuliah
				FROM tbl_mata_kuliah mp
				INNER JOIN tbl_nilai_mahasiswa ns
					ON ns.kode_mata_kuliah = mp.kode_mata_kuliah
				INNER JOIN tbl_kelas_mahasiswa ks
					ON ks.id_kelas_siswa = ns.id_kelas_siswa
				INNER JOIN tbl_kelas_detail kd
					ON kd.id_kelas_detail = ks.id_kelas_detail
				WHERE ks.nim='$nim'
				AND kd.tahun_ajaran='$tahun'
				GROUP BY mp.kode_mata_kuliah";
		$rec = $this->db->query($sql);
		$res = $rec->result_array();
		return $res;
	}
	
	function get_list_siswa_nilai($kode_dosen, $id_kelas_detail, $kode_mapel)
	{
		$sql = "
			SELECT
				ks.nim,
				s.nama,
				ks.id_kelas_siswa
			FROM tbl_kelas_mahasiswa ks
			INNER JOIN tbl_siswa s
				ON ks.nim = s.nim
			WHERE ks.id_kelas_detail = '$id_kelas_detail'
			UNION
			Select 
				kmm.nim,
				ss.nama,
				kmm.id_kelas_mahasiswa_matkul
			FROM tbl_kelas_mahasiswa_matkul kmm
			INNER JOIN tbl_siswa ss
				ON kmm.nim = ss.nim
			WHERE kmm.id_kelas_detail = '$id_kelas_detail'
			AND kmm.kode_mata_kuliah = '$kode_mapel'
			";
		$rec = $this->db->query($sql);
		$res = $rec->result_array();
		for($i=0; $i<count($res); $i++)
		{
			$sql2 = "
					SELECT
					ns.nilai
					FROM tbl_nilai_mahasiswa ns
					WHERE
						ns.id_kelas_siswa = '".$res[$i]['id_kelas_siswa']."'
						AND ns.kode_mata_kuliah = '$kode_mapel'
					";
			$rec2 = $this->db->query($sql2);
			$res2 = $rec2->result_array();
			$res[$i]['nilai'] = "";			
			if(count($res2)>0)
				$res[$i]['nilai'] = $res2[0]['nilai'];			
			
		}
		return $res;
	}
	
	function get_list_siswa_absensi($kode_dosen, $id_kelas_detail, $kode_mapel)
	{
		$sql = "
		SELECT
		ks.nim,
		s.nama,
		ks.id_kelas_siswa
		FROM tbl_kelas_mahasiswa ks
		INNER JOIN tbl_siswa s
		ON ks.nim = s.nim
		WHERE ks.id_kelas_detail = '$id_kelas_detail'
		";
		$rec = $this->db->query($sql);
		$res = $rec->result_array();
		
		// Ambil jumlah pertemuan dalam mata kuliah
		$sql2 = "SELECT pertemuan FROM tbl_mata_kuliah
				WHERE kode_mata_kuliah = '".$kode_mapel."'";
		$rec = $this->db->query($sql2);
		$resMatkul = $rec->result_array();
		$jmlPertemuan = 12;
		if(count($resMatkul)>0)
			$jmlPertemuan = $resMatkul[0]['pertemuan'];
		
		for($i=0; $i<count($res); $i++)
		{
			$res[$i]["isiAbsensi"]="";
				for($j=1; $j<=$jmlPertemuan; $j++)
				{
					$sql2 = "SELECT absensi FROM tbl_kelas_siswa_absensi
							WHERE kode_mata_kuliah = '".$kode_mapel."'
							AND id_kelas_detail = '".$id_kelas_detail."'
							AND nim = '".$res[$i]['nim']."'
							AND pertemuan_ke = '".$j."'";
					$rec = $this->db->query($sql2);
					$resAbsensi = $rec->result_array();
					if(count($resAbsensi)>0)
					{
						$res[$i]["isiAbsensi"][$j-1]=$resAbsensi[0]['absensi'];	
					}
					else
					{
						$res[$i]["isiAbsensi"][$j-1]="null";
					}
				}
		}
		return $res;
	}
	
	
	
	function get_list_siswa_nilai_siswa($nim, $kelas, $tahun, $semester) 
	{
		$sql = "SELECT
				ns.kode_mata_kuliah, mk.mata_kuliah, ns.nilai
				FROM tbl_nilai_mahasiswa ns
				INNER JOIN tbl_kelas_mahasiswa ks
					ON ks.id_kelas_siswa = ns.id_kelas_siswa
				INNER JOIN tbl_kelas_detail kd
					ON kd.id_kelas_detail = ks.id_kelas_detail
				INNER JOIN tbl_mata_kuliah mk 
					ON mk.kode_mata_kuliah = ns.kode_mata_kuliah
				WHERE
					kd.kode_kelas = '$kelas'
				AND kd.tahun_ajaran = '$tahun' 
				AND kd.semester = '$semester'
				AND ks.nim = '$nim'";		
		$rec = $this->db->query($sql);
		$res = $rec->result_array();
		return $res;
	}
	
	function get_list_siswa_nilai_siswa_transfer($nim, $tahun, $semester) 
	{
		$sql = "SELECT
				dt.mata_kuliah, dt.nilai
				FROM tbl_detail_nilai_mahasiswa_transfer dt
				INNER JOIN tbl_nilai_mahasiswa_transfer nt
					ON dt.id_nilai_mahasiswa_transfer = nt.id_nilai_mahasiswa_transfer
				WHERE
					nt.nim = '$nim'
				AND nt.tahun_ajaran = '$tahun' 
				AND nt.semester = '$semester'";		
		$rec = $this->db->query($sql);
		$res = $rec->result_array();
		return $res;
	}
	
	function get_kategori_nilai()
	{
			$sql = "
					SELECT
					kn.id_kategori_nilai,
					kn.kategori
					FROM tbl_kategori_nilai kn
					GROUP BY kn.id_kategori_nilai
					";
			$rec = $this->db->query($sql);
			$res = $rec->result_array();
		return $res;
	}
	
	function update_nilai($kd_mapel, $id_kelas_siswa, $nilai, $id_kelas_detail = "", $nimnya = "")
	{
		$sql = "SELECT
				id_nilai_mahasiswa
				FROM tbl_nilai_mahasiswa
				WHERE
				kode_mata_kuliah='$kd_mapel'
				AND id_kelas_siswa='$id_kelas_siswa'";
		$rec = $this->db->query($sql);

		$sql2 = "SELECT
				id_kelas_mahasiswa_matkul
				FROM tbl_kelas_mahasiswa_matkul
				WHERE
				kode_mata_kuliah='$kd_mapel'
				AND id_kelas_detail='$id_kelas_detail'
				AND nim='$nimnya'";
		$rec2 = $this->db->query($sql2);

		if($rec->num_rows()>0)
		{
			$sql = "UPDATE tbl_nilai_mahasiswa
					SET nilai='$nilai'
					WHERE
					kode_mata_kuliah='$kd_mapel'
					AND id_kelas_siswa='$id_kelas_siswa'";
			$rec = $this->db->query($sql);
		}
		else if($rec2->num_rows()>0)
		{
			$sql = "UPDATE tbl_kelas_mahasiswa_matkul
					SET nilai = '$nilai'
					WHERE
					kode_mata_kuliah='$kd_mapel'
					AND id_kelas_detail='$id_kelas_detail'
					AND nim='$nimnya'";
		}
		else
		{
			$sql = "INSERT INTO tbl_nilai_mahasiswa
								(nilai
								, kode_mata_kuliah
								, id_kelas_siswa)
								VALUES
								('$nilai'
								, '$kd_mapel'
								, '$id_kelas_siswa')";
			$rec = $this->db->query($sql);
				
		}
	}

	function update_absensi( $post_kode_mapel
							, $id_kelas_detail
							, $nimnya
							, $pertemuanKe
							, $scoreAbsensi)
	{
		$sql = "SELECT
		id_kelas_siswa_absensi
		FROM tbl_kelas_siswa_absensi
		WHERE
		id_kelas_detail			= '$id_kelas_detail'
		AND nim					= '$nimnya'
		AND kode_mata_kuliah	= '$post_kode_mapel'
		AND pertemuan_ke		= '$pertemuanKe'";
		$rec = $this->db->query($sql);
		if($rec->num_rows()>0)
		{
			$sql = "UPDATE tbl_kelas_siswa_absensi
			SET absensi				= '$scoreAbsensi'
			WHERE
			id_kelas_detail			= '$id_kelas_detail'
			AND nim					= '$nimnya'
			AND kode_mata_kuliah	= '$post_kode_mapel'
			AND pertemuan_ke		= '$pertemuanKe'";
			$rec = $this->db->query($sql);
		}
		else
		{
			$sql = "INSERT INTO tbl_kelas_siswa_absensi
			(id_kelas_detail
			, nim
			, kode_mata_kuliah
			, pertemuan_ke
			, absensi)
			VALUES
			('$id_kelas_detail'
			, '$nimnya'
			, '$post_kode_mapel'
			, '$pertemuanKe'
			, '$scoreAbsensi')";
			$rec = $this->db->query($sql);
		
		}
	}

	function get_iden_dosen($id)
	{
		$sql = "SELECT kode_dosen,nama FROM tbl_dosen where kode_dosen='$id'";
		
		$rec = $this->db->query($sql);
		$res = $rec->result_array();
		return $res;
	}


	function get_all_dosen()
	{
		$sql = "SELECT kode_dosen,nama FROM tbl_dosen order by nama asc";
		
		$rec = $this->db->query($sql);
		$res = $rec->result_array();
		return $res;
	}
	
}


